/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package AplManager.controller;



import java.sql.*;
import AplManager.views.*;
import AplManager.models.*;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.Set;
import javax.swing.JOptionPane;
/**

/**
 *
 * @author user
 */
public class DBcontroller {
    private Set  players;
    private TableDisplay view;
    Team model;
    ArrayList<Team> teams;
java.sql.Connection conn = null;

public DBcontroller(TableDisplay view,Team model){
this.view = view;
this.model =  model;
teams = new ArrayList<Team>();
}

public void set(){
 String id = model.getTeamID();
try {
  java.sql.Statement s = conn.createStatement();
  java.sql.ResultSet r = s.executeQuery("SELECT * FROM players WHERE teamId = " +"'"+ id +"'");
  while(r.next()) {
   Player player = new Player(r.getString("fname"),r.getString("lname"),
           r.getString("position"),r.getInt("number"));
           player.setGoals(r.getInt("goals"));
           player.setAssists(r.getInt("assists"));
           model.addPlayer(player);
           //Object[] row = {player.getFirstName(),player.getlastName()};
           Object[] row = {player.getFirstName(),player.getlastName(),player.getNumber()+"",
           player.getPosition(),player.getGoals()+"",player.getAssists()+"",false};
          view.addRows(row);
  }
 } catch (Exception e) {
  System.out.println(e);
  System.exit(0);
 }
model.printArray();
}
public void initialize(){ 
 try {
 Class.forName("com.mysql.jdbc.Driver").newInstance();
 conn = java.sql.DriverManager.getConnection(
 "jdbc:mysql://localhost/aplmanager?user=root&password=family");
 }
 catch (Exception e) {
  System.out.println(e);
  System.exit(0);
 } 
 System.out.println("Connection established");
 
}

public Boolean addRecord(){

 try{
    PreparedStatement p=conn.prepareStatement("Insert Into team set teamID=?, teamName=?");
    p.setString(1, model.getTeamID());
    p.setString(2, model.getTeamName());
    p.execute();  //use execute if no results expected back
    }catch(Exception e){
         JOptionPane.showMessageDialog(null,"Failed to create new team. Please try again");
                Menu menu = new Menu();
                menu.setVisible(true);
                MenuController control = new MenuController(menu);
                control.control();
                return false;
    }
    return true;
}

public void addRecords(){
deleteRecords(model.getTeamID());
for(int i =0;i< model.getCurSize(); i++){
 String fname = model.getPlayers()[i].getFirstName();
 String lname = model.getPlayers()[i].getlastName();
 int num =  model.getPlayers()[i].getNumber();
 String pos = model.getPlayers()[i].getPosition();
 int goals = model.getPlayers()[i].getGoals();
 int assists = model.getPlayers()[i].getAssists();
 try{
    PreparedStatement p=conn.prepareStatement("Insert Into players set fname=?, lname=? ,number=?, position=?, goals=?, assists=?, teamId=?");
    p.setString(1, fname);
    p.setString(2, lname);
    p.setInt(3, num);
    p.setString(4,pos);
    p.setInt(5, goals);
    p.setInt(6, assists);
    p.setString(7, model.getTeamID());
    p.execute();  //use execute if no results expected back
    }catch(Exception e){
        System.out.println("Error"+e.toString());
        return;        
    }
}
}


public void deleteRecords(String id){

 try{
    PreparedStatement p=conn.prepareStatement("Delete from players where teamId =?");
    p.setString(1, id);
    p.execute();  //use execute if no results expected back
    }catch(Exception e){
        System.out.println("Error"+e.toString());
        return;        
    }

}
    public void setModel(Team model){
    this.model = model;
    }
    
    public void setTeams(){
  TeamSelect tview = new TeamSelect();
try {
  java.sql.Statement s = conn.createStatement();
  java.sql.ResultSet r = s.executeQuery("SELECT * FROM team");
  Iterator it = teams.iterator();
 
  while(r.next() ) {
      String id =r.getString("teamId");
      String nme =r.getString("teamName");
   Team team = new Team(id,nme);
   teams.add(team);
   tview.addToTeamList(nme);
   it.next();
  }
 } catch (Exception e) {
  System.out.println(e);
  //System.exit(0);
 }
tview.setVisible(true);
TeamSelectController control = new TeamSelectController(tview,teams);
control.control();
}
    
    public ArrayList<Team> getTeams(){
    return teams;
    }
}
